package com.zhao.fishingguideserver.mapper;

import com.zhao.fishingguideserver.POJO.User;
import org.apache.ibatis.annotations.*;

import java.util.List;
import java.util.Map;

/**
 * 用户数据访问接口
 */
@Mapper
public interface UserMapper {
    @Select("select user_id,username,password,nick_name as nickName,created_at as createdAt from users where username=#{username}")
    User selectUserByName(String username);

    @Select("select user_id,username,password,nick_name as nickName,created_at as createdAt from users where user_id=#{userId}")
    User selectUserById(Long userId);

    @Insert("insert into users(username,password,created_at) values(#{username},#{password},now())")
    void register(User user);

    /**
     * 更新用户信息（动态SQL，只更新非空字段）
     */
    @Update("<script>" +
            "update users " +
            "<set>" +
            "<if test='nickName != null'>nick_name = #{nickName},</if>" +
            "<if test='password != null'>password = #{password},</if>" +
            "</set>" +
            "where user_id = #{user_id}" +
            "</script>")
    int updateUser(User user);
    
    // ==================== 管理端接口 ====================
    
    /**
     * 查询用户列表（管理端，带搜索和筛选）
     */
    @Select("<script>" +
            "SELECT user_id, username, nick_name as nickName, role, status, " +
            "created_at as createdAt, last_login_time as lastLoginTime " +
            "FROM users " +
            "<where>" +
            "<if test='username != null and username != \"\"'>" +
            "AND username LIKE CONCAT('%', #{username}, '%')" +
            "</if>" +
            "<if test='nickName != null and nickName != \"\"'>" +
            "AND nick_name LIKE CONCAT('%', #{nickName}, '%')" +
            "</if>" +
            "<if test='status != null'>" +
            "AND status = #{status}" +
            "</if>" +
            "</where>" +
            "ORDER BY created_at DESC " +
            "</script>")
    List<User> selectAllUsers(@Param("username") String username,
                              @Param("nickName") String nickName,
                              @Param("status") Integer status);
    
    /**
     * 统计用户数量（管理端，带搜索和筛选）
     */
    @Select("<script>" +
            "SELECT COUNT(1) FROM users " +
            "<where>" +
            "<if test='username != null and username != \"\"'>" +
            "AND username LIKE CONCAT('%', #{username}, '%')" +
            "</if>" +
            "<if test='nickName != null and nickName != \"\"'>" +
            "AND nick_name LIKE CONCAT('%', #{nickName}, '%')" +
            "</if>" +
            "<if test='status != null'>" +
            "AND status = #{status}" +
            "</if>" +
            "</where>" +
            "</script>")
    int countUsers(@Param("username") String username,
                   @Param("nickName") String nickName,
                   @Param("status") Integer status);
    
    /**
     * 分页查询用户（管理端，带搜索和排序）
     */
    @Select("<script>" +
            "SELECT user_id, username, nick_name as nickName, role, status, " +
            "created_at as createdAt, last_login_time as lastLoginTime " +
            "FROM users " +
            "<where>" +
            "<if test='username != null and username != \"\"'>" +
            "AND username LIKE CONCAT('%', #{username}, '%')" +
            "</if>" +
            "<if test='nickName != null and nickName != \"\"'>" +
            "AND nick_name LIKE CONCAT('%', #{nickName}, '%')" +
            "</if>" +
            "<if test='status != null'>" +
            "AND status = #{status}" +
            "</if>" +
            "</where>" +
            "ORDER BY " +
            "<choose>" +
            "  <when test='sortBy == \"username\"'> username </when>" +
            "  <when test='sortBy == \"createdAt\"'> created_at </when>" +
            "  <otherwise> created_at </otherwise>" +
            "</choose> " +
            "<choose>" +
            "  <when test='order == \"ASC\"'> ASC </when>" +
            "  <otherwise> DESC </otherwise>" +
            "</choose> " +
            "LIMIT #{offset}, #{limit}" +
            "</script>")
    List<User> selectUsersPaged(@Param("username") String username,
                                @Param("nickName") String nickName,
                                @Param("status") Integer status,
                                @Param("sortBy") String sortBy,
                                @Param("order") String order,
                                @Param("offset") Integer offset,
                                @Param("limit") Integer limit);
    
    /**
     * 获取用户详细信息（含统计数据）
     */
    @Select("SELECT u.user_id, u.username, u.nick_name as nickName, u.role, u.status, " +
            "u.created_at as createdAt, u.last_login_time as lastLoginTime, " +
            "(SELECT COUNT(*) FROM product WHERE user_id = u.user_id) as productCount, " +
            "(SELECT COUNT(*) FROM orders WHERE buyer_id = u.user_id) as buyOrderCount, " +
            "(SELECT COUNT(*) FROM orders WHERE seller_id = u.user_id) as sellOrderCount, " +
            "(SELECT COUNT(*) FROM posts WHERE user_id = u.user_id AND status = 'published') as postCount, " +
            "(SELECT COUNT(*) FROM comments WHERE user_id = u.user_id AND status = 'published') as commentCount, " +
            "(SELECT COUNT(*) FROM favorites WHERE user_id = u.user_id) as favoriteCount " +
            "FROM users u WHERE u.user_id = #{userId}")
    Map<String, Object> selectUserDetail(Long userId);
    
    /**
     * 更新用户状态
     */
    @Update("UPDATE users SET status = #{status} WHERE user_id = #{userId}")
    int updateUserStatus(@Param("userId") Long userId, @Param("status") Integer status);
    
    /**
     * 删除用户
     */
    @Delete("DELETE FROM users WHERE user_id = #{userId}")
    int deleteUser(Long userId);
    
    /**
     * 重置用户密码
     */
    @Update("UPDATE users SET password = #{password} WHERE user_id = #{userId}")
    int resetPassword(@Param("userId") Long userId, @Param("password") String password);
}
